package org.sauceggplant.item.man.dao;

import com.alibaba.fastjson2.JSON;
import org.sauceggplant.item.man.dto.ItemDto;
import org.sauceggplant.item.man.dto.SqlDto;
import org.sauceggplant.item.man.po.Item;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ItemDao extends AbstractDao {

    private static final Logger logger = LoggerFactory.getLogger(ItemDao.class);

    private static final String INSERT_SQL = "insert into `t_item` (`uuid`,`createAccount`,`updateAccount`,`createTime`,`updateTime`,`version`,`typeId`,`name`,`count`,`remark`) values (?,?,?,?,?,?,?,?,?,?)";

    private static final String UPDATE_SQL = "update `t_item` set `version` = ? , `updateTime` = ? ";

    private static final String UPDATE_SQL_END = " where `id` = ? and `version` = ? ";

    private static final String DELETE_SQL = "delete from `t_item` where `id` = ?";

    private static final String SELECT_SQL = "select `id`,`uuid`,`createAccount`,`updateAccount`,`createTime`,`updateTime`,`version`,`typeId`,`name`,`count`,`remark` from `t_item`";

    public List<Item> findAll(ItemDto itemDto, String dbType) throws SQLException {
        SqlDto sqlDto = new SqlDto();
        List<Object> params = new ArrayList<>();
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(SELECT_SQL);
        int condition = 0;
        if (null != itemDto.getNameIsLike()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `name` like ? ");
            params.add("%" + itemDto.getNameIsLike() + "%");
            ++condition;
        }
        if (null != itemDto.getRemarkIsLike()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `remark` like ? ");
            params.add("%" + itemDto.getRemarkIsLike() + "%");
            ++condition;
        }
        if (null != itemDto.getCreateTimeBefore()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `createTime` < ? ");
            params.add(itemDto.getCreateTimeBefore());
            ++condition;
        }
        if (null != itemDto.getCreateTimeAfter()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `createTime` > ? ");
            params.add(itemDto.getCreateTimeAfter());
            ++condition;
        }
        if (null != itemDto.getUpdateTimeBefore()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `updateTime` < ? ");
            params.add(itemDto.getUpdateTimeBefore());
            ++condition;
        }
        if (null != itemDto.getUpdateTimeAfter()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `updateTime` > ? ");
            params.add(itemDto.getUpdateTimeAfter());
            ++condition;
        }
        if (null != itemDto.getUuid()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `uuid` = ? ");
            params.add(itemDto.getUuid());
            ++condition;
        }
        if (null != itemDto.getCreateAccount()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `createAccount` = ? ");
            params.add(itemDto.getCreateAccount());
            ++condition;
        }
        if (null != itemDto.getUpdateAccount()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `updateAccount` = ? ");
            params.add(itemDto.getUpdateAccount());
            ++condition;
        }
        if (null != itemDto.getCreateTime()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `createTime` = ? ");
            params.add(itemDto.getCreateTime());
            ++condition;
        }
        if (null != itemDto.getUpdateTime()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `updateTime` = ? ");
            params.add(itemDto.getUpdateTime());
            ++condition;
        }
        if (null != itemDto.getVersion()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `version` = ? ");
            params.add(itemDto.getVersion());
            ++condition;
        }
        if (null != itemDto.getTypeId()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `typeId` = ? ");
            params.add(itemDto.getTypeId());
            ++condition;
        }
        if (null != itemDto.getName()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `name` = ? ");
            params.add(itemDto.getName());
            ++condition;
        }
        if (null != itemDto.getCount()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `count` = ? ");
            params.add(itemDto.getCount());
            ++condition;
        }
        if (null != itemDto.getRemark()) {
            if (condition == 0) {
                stringBuffer.append(" where");
            } else {
                stringBuffer.append(" and");
            }
            stringBuffer.append(" `remark` = ? ");
            params.add(itemDto.getRemark());
            ++condition;
        }
        if (null != itemDto.getOrder()) {
            stringBuffer.append(" order by ? ");
            params.add(itemDto.getOrder());
        }
        if (null != itemDto.getOffset()) {
            stringBuffer.append(" limit ?,? ");
            params.add(itemDto.getOffset());
            params.add(itemDto.getSize());
        }
        sqlDto.setParams(params);
        sqlDto.setType(dbType);
        sqlDto.setSql(stringBuffer.toString());
        getLogger().info(JSON.toJSONString(sqlDto));
        ResultSet resultSet = sqlExecuteService.executeQuery(sqlDto);
        return buildItemList(resultSet);
    }

    public List<Item> buildItemList(ResultSet resultSet) throws SQLException {
        List<Item> itemList = new ArrayList<>();
        while (resultSet.next()) {
            Item item = new Item();
            item.setId(resultSet.getLong(1));
            item.setUuid(resultSet.getLong(2));
            item.setCreateAccount(resultSet.getString(3));
            item.setUpdateAccount(resultSet.getString(4));
            item.setCreateTime(resultSet.getTime(5));
            item.setUpdateTime(resultSet.getTime(6));
            item.setVersion(resultSet.getInt(7));
            item.setTypeId(resultSet.getLong(8));
            item.setName(resultSet.getString(9));
            item.setCount(resultSet.getString(10));
            item.setRemark(resultSet.getString(11));
            itemList.add(item);
        }
        getLogger().debug("findAll.item.res:{}", JSON.toJSONString(itemList));
        return itemList;
    }

    public int insert(Item item, String dbType) throws SQLException {
        SqlDto sqlDto = new SqlDto();
        sqlDto.setType(dbType);
        sqlDto.setSql(INSERT_SQL);
        List<Object> params = new ArrayList<Object>();
        params.add(item.getUuid());
        params.add(item.getCreateAccount());
        params.add(item.getUpdateAccount());
        params.add(item.getCreateTime());
        params.add(item.getUpdateTime());
        params.add(item.getVersion());
        params.add(item.getTypeId());
        params.add(item.getName());
        params.add(item.getCount());
        params.add(item.getRemark());
        sqlDto.setParams(params);
        return insert(sqlDto);
    }

    public int update(Item item, String dbType) throws SQLException {
        SqlDto sqlDto = new SqlDto();
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(UPDATE_SQL);
        List<Object> params = new ArrayList<Object>();
        params.add(item.getVersion() + 1);
        params.add(new Date());
        if (null != item.getUuid()) {
            params.add(item.getUuid());
            stringBuffer.append(", `uuid` = ? ");
        }
        if (null != item.getUpdateAccount()) {
            params.add(item.getUpdateAccount());
            stringBuffer.append(", `updateAccount` = ?");
        }
        if (null != item.getTypeId()) {
            params.add(item.getTypeId());
            stringBuffer.append(", `typeId` = ?");
        }
        if (null != item.getName()) {
            params.add(item.getName());
            stringBuffer.append(", `name` = ?");
        }
        if (null != item.getCount()) {
            params.add(item.getCount());
            stringBuffer.append(", `count` = ?");
        }
        if (null != item.getRemark()) {
            params.add(item.getRemark());
            stringBuffer.append(", `remark` = ?");
        }
        params.add(item.getId());
        params.add(item.getVersion());
        sqlDto.setParams(params);
        stringBuffer.append(UPDATE_SQL_END);
        sqlDto.setSql(stringBuffer.toString());
        sqlDto.setType(dbType);
        return update(sqlDto);
    }

    public int delete(Long id, String dbType) throws SQLException {
        SqlDto sqlDto = new SqlDto();
        sqlDto.setSql(DELETE_SQL);
        sqlDto.setType(dbType);
        List<Object> params = new ArrayList<Object>();
        params.add(id);
        sqlDto.setParams(params);
        return update(sqlDto);
    }

    @Override
    protected Logger getLogger() {
        return logger;
    }
}
